<?php
require_once('config/config.inc');
require_once('lib.inc');
	// Provides functions for user account API calls
	class Sticky{
		function __construct($rwConn,$userInfo){
			// Check session for current login
			// If exists validate hash then fill out members
			$this->rwConn = $rwConn;
			$this->userId = $userInfo['userId'];
			global $paramArray;
			if(isset($paramArray['stickyId']))
			{
				$this->stickyId=$paramArray['stickyId'];
			}
			else
				$this->stickyId=NULL;
			$this->STORY="story";
			$this->DEFECT="defect";
		}

		function viewHistory(){
			global $paramArray;
			$stickyId = (isset($paramArray['stickyId']))?$paramArray['email']:false;
			if(is_null($this->stickyId)){
				$retVal['message'] = "stickyId not included";
				return $retVal;
			}
			
			$sqlQuery = "SELECT user_id,sticky_action,date_time from History WHERE sticky_id=" . $this->stickyId;
			$result = $this->rwConn->query($sqlQuery);
			checkDbError();
			//TODO add here rwConn->affected_rows for a select rwConn->num_rows
			$retVal['success'] = "true";
			$retVal['message'] = "";
			$history_arr = array();
			while($row = $result->fetch_assoc()){
				$history_arr[] = $row;
			}
			$retVal['history'] = $history_arr;
			return $retVal;
		}
		private function insertHistory($userId, $stickyAction){
			$sqlQuery = "INSERT INTO History (sticky_id, user_id, sticky_action, date_time) VALUES(".$this->stickyId.",".$userId.",'".$stickyAction."', now())";
			checkDbError();
			$result = $this->rwConn->query($sqlQuery);
			if($this->rwConn->affected_rows != 1)
				return false;
			return true;
			print_r($resultSet);
		}

		private function checkStickyAction($sticky_action){
		
			switch($sticky_action){
			
				case 'deleted':
					return true;
				case 'undeleted':
					return true;
				case 'created':
					return true;
				case 'modified':
					return true;
				default:
					return false;	
			}
		}
		private  function checkCategory($category){
                	switch($category){
                                case $this->STORY:
                                        return true;
                                        break;
                                case $this->DEFECT:
                                        return true;
                                        break;
                                default:
                                        return false;


                        }
                }

		
		private $rwConn;
		private $stickyId;
		private $userId;
		 private $STORY = "story";
                 private $DEFECT = "defect";


		function getSticky(){

			global $paramArray;
			if(is_null($this->stickyId))
			{
				$retVal['success'] = FALSE;
				$retVal['message'] = "Bad API call";
				return $retVal;	
			}
			$this->stickyId = $paramArray['stickyId'];
			$stickyInfo=$this->rwConn->query("Select * from Sticky where sticky_id='$this->stickyId'");
			
			checkDbError();
			if($stickyInfo->num_rows == 1)
			{
				//fetch array
				$stickyRow =$stickyInfo->fetch_assoc();
				//get the author's first name and last name
				$authorInfo = $this->rwConn->query("Select first_name, last_name from User where user_id = '".$stickyRow['author_id']."'");
			checkDbError();
				$authorName = $authorInfo->fetch_assoc();
				$stickyRow['author_name'] = $authorName['first_name']." ".$authorName['last_name'];
				//get the state (and parent state (if applicalbe))
				$stateInfo = $this->rwConn->query("Select * from Sticky_State where state_id='".$stickyRow['state_id']."'");
				checkDbError();
				$stateRow = $stateInfo->fetch_assoc();
				$stickyRow['state_name']=$stateRow['state_name'];
				if(!is_null($stateRow['parent_id']))
				{
					$parentInfo = $this->rwConn->query("Select * from  Sticky_State where state_id='".$stateRow['parent_id']."'");
					checkDbError();
					$parentRow = $parentInfo->fetch_assoc();
					$stickyRow['parent_id']=$stateRow['parent_id'];
					$stickyRow['parent_name']=$parentRow['state_name'];
				}
				//get the pirority and severity of said sticky
				if(!is_null($stickyRow['priority_id'])){
					$priorityInfo = $this->rwConn->query("Select description from Priority where priority_id = '".$stickyRow['priority_id']."'");
					checkDbError();
					$priorityDesc = $priorityInfo->fetch_assoc();
					$stickyRow['priority_desc'] = $priorityDesc['description'];
				}
				//get the severity of the defect if is set
				if(!is_null($stickyRow['severity_id'])) {
					 $severityInfo = $this->rwConn->query("Select description from Severity where severity_id = '".$stickyRow['severity_id']."'");
					checkDbError();
                                        $severityDesc = $severityInfo->fetch_assoc();
                                        $stickyRow['severity_desc'] = $severityDesc['description'];
				}
				if($stickyRow['category'] == "defect"){
					$defectStateInfo = $this->rwConn->query("Select description from Defect_State where defect_state_id='".$stickyRow['defect_state_id']."'");
					checkDbError();
					$defectName = $defectStateInfo->fetch_assoc();
					$stickyRow['defect_state_desc'] =  $defectName['description']; 	
				}
				
			}
			else
			{
				$retVal['success'] = false;
				$retVal['message'] = "Bad Sticky ID.";
				return $retVal;
			}
			$retVal['success'] = true;
			foreach ($stickyRow as $key=>$value)
			{
				$retVal[$key]= $value;
			}
			return $retVal;
		}
		function removeSticky()
		{
			if(is_null($this->stickyId))
			{
				$retVal['message'] = "Bad API Call";
				return $retVal;
			}
			$resultQ = $this->rwConn->query("Update Sticky set is_deleted=1 where sticky_id='{$this->stickyId}'");
			checkDbError();
			if($this->rwConn->affected_rows != 1 )
			{
				$retVal['message'] = "Bad Sticky ID/Sticky is already deleted";
				return $retVal;
			}
			
			$this->insertHistory($this->userId, 'deleted');
			$retVal['success'] = true;
			return $retVal;
		}
		function unRemoveSticky()
		{
			if(is_null($this->stickyId))
			{
				$retVal['message'] = "Bad API Call";
				return $retVal;
			}
			$resultQ = $this->rwConn->query("Update Sticky set is_deleted=0 where sticky_id='{$this->stickyId}'");
			checkDbError();
			if($this->rwConn->affected_rows != 1 )
			{
				$retVal['message'] = "Bad Sticky ID/Sticky is already undeleted.";
				return $retVal;
			}
			$this->insertHistory($this->userId, 'undeleted');
			$retVal['success'] = true;
			return $retVal;
		}
		/*This function checks if the data passed to
			addSticky
			editSticky
		 is valid. Returns error array if anyhting is bad
		use isset($retval['errors']) to see if anythign went bad.
		*/
		private function checkStickyData()
		{
			global $paramArray;
			$retVal = null;	
			if(!isset($paramArray['category']))
			{
				$retVal['message'] = "Bad API Call";
				$retVal['errors']['category'] = "Category Not set";
				return $retVal;
			}
			if($this->userId == NULL)
			{
				$retVal['message'] = "You must be logged in to do that";
				$retVal['errors']['user'] = "You are not logged in.";
				return $retVal;
			}

			if(!isset($paramArray['release_id'])){
				$retVal['message'] = "Bad API Call";
				$retVal['errors']['release_id'] = "release_id not set";
				return $retVal;	
			}
			$sqlQuery = "SELECT release_id FROM `Release` where release_id=";
			$sqlQuery = $sqlQuery . $paramArray['release_id'];
			$updateReturn = $this->rwConn->query($sqlQuery);
			$Echeck = checkDbError(1);
			if($Echeck['message'] === "Database Error" || $this->rwConn->affected_rows !=1){
				$retVal['message'] = "Bad API Call";
				$retVal['errors']['release_id'] = "Invalid release_id";
				return $retVal;
			}
			$userCheckSql = " select user_id from `Release` rls, UID_Project_Association u where proj_id=project_id AND release_id='{$paramArray['release_id']}' and user_id ={$this->userId};";
			$userCheckReturn = $this->rwConn->query($sqlQuery);
			checkDbError();
			if($userCheckReturn->num_rows == 0)
			{
				$retVal['message'] = "Security Exception: You are not authorized to do that";
				$retVal['errors']['security'] = "You are not allowed to do that";
				return $retVal;
			}
			if($paramArray['category'] !== $this->STORY && $paramArray['category'] !== $this->DEFECT)
			{
				$retVal['message'] = "Bad API Call";
				$retVal['errors']['category'] = "Invalid Category";
				return $retVal;
			}
			if(!isset($paramArray['state_id'])){
				$retVal['message'] = "Bad API Call";
				$retVal['errors']['state_id'] = "state_id not set";
				return $retVal;
			}
                        $sqlQuery = "SELECT state_id FROM Sticky_State WHERE state_id =".$paramArray['state_id'];
			$updateReturn = $this->rwConn->query($sqlQuery);
			$Echeck = checkDbError(1);
			if($Echeck['message'] === "Database Error" || $this->rwConn->affected_rows !=1){
				$retVal['message'] = "Bad API Call";
				$retVal['errors']['state_id'] = "Invalid state_id";
				return $retVal;
			}
			
			if($paramArray['category'] === $this->DEFECT){
				if(!isset($paramArray['priority_id'])){
					$retVal['message'] = "Bad API Call";
					$retVal['errors']['priority_id'] = "priority_id not set";
					return $retVal;	
				}
				$sqlQuery = "SELECT priority_id FROM Priority WHERE priority_id =";
				$sqlQuery = $sqlQuery . $paramArray['priority_id'];
				
                       	 	$updateReturn = $this->rwConn->query($sqlQuery);
                        	$Echeck = checkDbError(1);
                        	if($Echeck['message'] === "Database Error" || $this->rwConn->affected_rows !=1){
                                	$retVal['message'] = "Bad API Call";
                                	$retVal['errors']['priority_id'] = "Invalid priority_id";
                                	return $retVal;
				}
				if(!isset($paramArray['defect_state_id'])){
					$retVal['message'] = "Bad API Call";
					$retVal['errors']['defect_state_id'] = "defect_state_id not set";
					return $retVal;	
				}
				$sqlQuery = "SELECT defect_state_id FROM Defect_State WHERE defect_state_id=";
				$sqlQuery = $sqlQuery . $paramArray['defect_state_id'];
                       	 	$updateReturn = $this->rwConn->query($sqlQuery);
                        	$Echeck = checkDbError(1);
                        	if($Echeck['message'] === "Database Error" || $this->rwConn->affected_rows !=1){
                                	$retVal['message'] = "Bad API Call";
                                	$retVal['errors']['defect_state_id'] = "Invalid defect_state_id";
                                	return $retVal;
				}
				if(!isset($paramArray['severity_id'])){
					$retVal['message'] = "Bad API Call";
					$retVal['errors']['severity_id'] = "severity_id not set";
					return $retVal;	
				}
				$sqlQuery = "SELECT severity_id FROM Severity WHERE severity_id=".$paramArray['severity_id'];
                       	 	$updateReturn = $this->rwConn->query($sqlQuery);
                        	$Echeck = checkDbError(1);
                        	if($Echeck['message'] === "Database Error" || $this->rwConn->affected_rows !=1){
                                	$retVal['message'] = "Bad API Call";
                                	$retVal['errors']['severity_id'] = "Invalid severity_id";
                                	return $retVal;
				}
				
				
                        }

			if(!isset($paramArray['color']) || empty($paramArray['color']))
			{
				$retVal['message'] = "Bad Arguments";
				$retVal['errors']['color'] = "You must specify a color";
			}
			//check if note is empty
			if(!isset($paramArray['note']) || empty($paramArray['note']))
			{
				$retVal['message'] = "Bad Arguments";
				$retVal['errors']['note'] = "You must specify a note";
			}
			//check if title is empty
			if(!isset($paramArray['title']) || empty($paramArray['title']))
			{
				$retVal['message']="Bad Arguments";
				$retVal['errors']['title'] = "You must specify a title";
			}
		 

			if(!is_null($this->stickyId))
			{
				//check if the last updated is ok
				$query =  "Select last_updated from Sticky where sticky_id = '{$this->stickyId}'";
				$lastUpdated= $this->rwConn->query($query);
				$lastUp = $lastUpdated->fetch_assoc();
				if(strcmp($lastUp['last_updated'],$paramArray['last_updated']))
				{
					$retVal['success'] = false;
					$retVal['message'] = "Sticky has changed!";
					$retVal['errors']['last_updated'] =  "This sticky has changed, please review the changes and resubmit.";
				}
			}	
		return $retVal;	
		}
		function editSticky()
		{
			global $paramArray;
			$return=$this->checkStickyData();
			if(isset($return['errors']))
			{
				return $return;
			}
			//data validated successfuly
			if($this->checkCategory($paramArray['category']))
			{
				//stories only can edit state, priority, note, title, color
				if(!strcmp($paramArray['category'], $this->STORY))
				{
					$query=" Update Sticky set state_id='{$paramArray['state_id']}', ";
					$query = $query . "priority_id='{$paramArray['priority_id']}',";
					$query = $query . "note='{$paramArray['note']}',title='{$paramArray['title']}',";
					$query = $query . "release_id='{$paramArray['release_id']}',color='{$paramArray['color']}', last_updated=now()";
					$query = $query . "where sticky_id='{$this->stickyId}'";
					$updateReturn = $this->rwConn->query($query);
					checkDbError();
					if($this->rwConn->affected_rows !=1)
					{
						$retVal['message']="Nothing Changed.";
						return $retVal;
					}
					$retVal['success'] = true;
					return $retVal;
				}
				//defects can edit state, priority, severity, defect_state, note, title, color
				else if (!strcmp($paramArray['category'], $this->DEFECT))
				{
					$query="Update Sticky set state_id='{$paramArray['state_id']}',";
					$query = $query . " priority_id='{$paramArray['priority_id']}',severity_id='{$paramArray['severity_id']}',";
					$query = $query . "note='{$paramArray['note']}',title='{$paramArray['title']}',";
					$query = $query . "release_id='{$paramArray['release_id']}',color='{$paramArray['color']}', defect_state_id='{$paramArray['defect_state+id']}'";
					$query = $query . " ,last_updated=now() where sticky_id='{$this->stickyId}'";
					$updateReturn = $this->rwConn->query($query);
					checkDbError();
					if($this->rwConn->affected_rows !=1)
					{
						$retVal['message']="Nothing Changed.";
						return $retVal;
					}
					$retVal['success'] =true;
					return $retVal;
				}
			}
			$retVal['message'] = "What is this I don't even";
			return $retVal;
		}
		function addSticky()
                {
                        global $paramArray;

			$return = $this->checkStickyData();
			if(!isset($return['errors'])){
				if($paramArray['category'] === "story"){
					$paramArray['defect_state_id'] = "NULL";
					$paramArray['severity_id'] = "NULL";
					$paramArray['priority_id'] = "NULL";
				}
			//we need to get the bottom ordering in the selected state id
			//max ordering from same state and same release
			$sqlQuery =  "SELECT max(ordering) from Sticky where state_id={$paramArray['state_id']} AND release_id={$paramArray['release_id']}";
			$orderingInfo = $this->rwConn->query($sqlQuery);
			checkDbError();
			$orderingMax =$orderingInfo->fetch_assoc();
			$newOrder = $orderingMax['max(ordering)']+1;
                        $sqlQuery = "INSERT INTO Sticky (author_id,category,state_id,ordering,priority_id,severity_id,";
			$sqlQuery = $sqlQuery . "release_id,note,title,color,is_deleted,defect_state_id,last_updated) VALUES(".$this->userId.",";
			$sqlQuery = $sqlQuery . "'".$paramArray['category']."',".$paramArray['state_id'].",{$newOrder},";
			$sqlQuery = $sqlQuery.$paramArray['priority_id'].",".$paramArray['severity_id'].",";
			$sqlQuery = $sqlQuery.$paramArray['release_id'].",'".$paramArray['note']."','".$paramArray['title']."',";
			$sqlQuery = $sqlQuery."'".$paramArray['color']."',0,".$paramArray['defect_state_id'].",now())";
			$insertResult = $this->rwConn->query($sqlQuery);
			checkDbError();
			if($this->rwConn->affected_rows != 1)
			{
				$retVal['message'] = "General Failure: Contact administrator.";
				return $retVal;
			}
			$retVal['success'] = true;
			$retVal['sticky_id'] = $this->rwConn->insert_id;
			return $retVal;
			}
			return $return;
			

                }
                function getId()
		{
			global $paramArray;
			$sqlQuery = "";
			$retKey = "";
			if(isset($paramArray['state'])){
                        	$sqlQuery = "SELECT state_id,state_name,parent_id FROM Sticky_State";
				$retKey = "state";
			}
			else if(isset($paramArray['severity'])){
				$sqlQuery = "SELECT severity_id, description FROM Severity";
				$retKey = "severity";
			}
			else if(isset($paramArray['priority'])){
				$sqlQuery = "SELECT priority_id, description FROM Priority";
				$retKey = "priority";
			}
			else if(isset($paramArray['defect'])){
				$sqlQuery = "SELECT defect_state_id, description FROM Defect_State";
				$retKey = "defect";
			}
			else{
				$retVal['message'] = "Field does not exist";
				return $retVal;
			}
			$retVal['success'] = "true";
			$result = $this->rwConn->query($sqlQuery);
			checkDbError();
			$toReturn = array();
			while($row = $result->fetch_assoc()){
				$toReturn[] = $row;
			}
			$retVal[$retKey] = $toReturn;
			return $retVal;
                }
		 function moveSticky()
                {
                        global $paramArray;
                        $position = isset($paramArray['position'])?$paramArray['position']:null;
                        $relativeTo = isset($paramArray['relativeTo'])?$paramArray['relativeTo']:null;
			if(is_null($this->stickyId)){
				return array('success' => false, 'message' => 'stickyId not specified');
			}
                        $allowedPosition = array("after","before","to");
                        if(is_null($position) || !in_array($position,$allowedPosition)){
                                return array('success' => false, 'message' => 'Position can either be before, after or to');
                        }
                        if(is_null($relativeTo)){
                                return array('success' => false, 'message' => 'Sticky has to be moved in relative to some other sticky');
                        }
                        if($relativeTo == $this->stickyId){
                                return array('success' => false, 'message' => 'Cannot move sticky relative to itself');
                        }
                        if($position == 'to') return $this->_moveStickyColumn($relativeTo);
                        return $this->_reorderSticky($position,$relativeTo);
                }
                private function _reorderSticky($position,$stickyId){
			$release = $this->_getMyRelease($stickyId);
			$state = $this->_getMyState($stickyId);
			if($release === false || $state === false){
				return array("success" => false, "message" => "relativeTo Sticky ID not found");
			}
			$sql = "SELECT sticky_id, ordering, state_id FROM Sticky where release_id = {$release} AND state_id={$state} order by ordering asc";
			$result = $this->rwConn->query($sql);
			checkDbError();
			$before = array();
			$after = array();
			$switched = false;
			while($row = $result->fetch_assoc()){
				$row['changed'] = false;
				$row['newOrdering'] = null;
				if($row['sticky_id'] == $this->stickyId) continue;
				if($switched) $after[]=$row;
				else{
					if($row['sticky_id'] == $stickyId){
						$switched = true;
						if($position == 'before')
							$after[] = $row;
						else
							$before[] = $row;
					}else{
						$before[]=$row;
					}
				}
			}
			$thisSticky = array(array('sticky_id'=>$this->stickyId,'ordering'=>-100,'newOrdering'=>null, 'state_id'=>$state));
			$newOrdering = array_merge($before,$thisSticky,$after);
			$i = 0;
			foreach($newOrdering as &$sticky){
				$sticky['newOrdering'] = $i++;
				if($sticky['newOrdering'] != $sticky['ordering']){
					$sql = "UPDATE Sticky set ordering = '{$sticky['newOrdering']}' WHERE sticky_id='{$sticky['sticky_id']}' LIMIT 1";
					$this->rwConn->query($sql);
					checkDbError();
				}
			}
			return array("success"=> true, "message" => "{$this->stickyId} moved {$position} {$stickyId}");
			

                }
                private function _moveStickyColumn($stateId){
			$sql = "SELECT parent_id,state_id FROM Sticky_State WHERE state_id = '{$stateId}'";
			$result = $this->rwConn->query($sql);
			checkDbError();
			if(!$result->num_rows){
				return array("success" => false, "message" => "Invalid State ID");
			}
			list($parent_id,$state_id) = $result->fetch_array();
			$dest = $state_id;
			/*if(!is_null($parent_id) && $parent_id){
				$dest = $parent_id;	
			}*/
                        $sql = "SELECT max(ordering) FROM Sticky WHERE state_id = '{$dest}'";
			$result = $this->rwConn->query($sql);
			checkDbError();
			$max = 0;
			if($result->num_rows)
				list($max) = $result->fetch_array();
			$max++;
			$sql = "UPDATE Sticky set ordering = $max, state_id = $state_id WHERE sticky_id = '{$this->stickyId}' LIMIT 1";
			$this->rwConn->query($sql);
			checkDbError();
			if($this->rwConn->affected_rows != 1){
				return array("success" => false, "message" => "Database Failure");
			}
			return array("success" => true, "message" => "Sticky has been moved");


                }
		private function _getMyState($stickyId = null){
			if(is_null($stickyId))$stickyId = $this->stickyId;
			$sql = "SELECT state_id FROM Sticky WHERE sticky_id = '{$stickyId}'";
			$result = $this->rwConn->query($sql);
			checkDbError();
			if(!$result->num_rows){
				return false;
			}
			list($stateId) = $result->fetch_array();
			return $stateId;
		}
                private function _getMyRelease($stickyId = null){
			if(is_null($stickyId))$stickyId = $this->stickyId;
			$sql = "SELECT release_id FROM Sticky WHERE sticky_id = '{$stickyId}'";
			$result = $this->rwConn->query($sql);
			checkDbError();
			if(!$result->num_rows){
				return false;
			}
			list($releaseId) = $result->fetch_array();
			return $releaseId;
                }



	};

